-- enable database mail for this instance
EXECUTE sys.sp_configure 'show advanced', 1;
RECONFIGURE;
EXECUTE sys.sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
EXECUTE sys.sp_configure 'show advanced', 0;
RECONFIGURE;
GO

USE msdb;
GO

-- create a database mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = N'DBA Notification Profile',
    @description = N'SQL Server Database Mail Notification Profile';
GO

-- add the public principal to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = N'DBA Notification Profile',
    @principal_name = N'public',
    @is_default = 1;
GO

-- create a database mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = N'DBA Notification Account',
    @description = N'SQL Server Database Mail Notification Account',
    @email_address = N'john@jdoe.com',
    @replyto_address = N'john@jdoe.com',
    @display_name = N'SQL Server Database Mail Notification',
    @mailserver_name = N'smtp.jdoe.com',
	@mailserver_type = N'SMTP',
	@port = 25,
	@username = N'john@jdoe.com',
	@password = N'',
	@use_default_credentials = 0,
	@enable_ssl = 0;
GO

-- add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = N'DBA Notification Profile',
    @account_name = N'DBA Notification Account',
    @sequence_number = 1;
GO

-- send a test email
USE msdb;
EXECUTE AS USER = 'dbo';

EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA Notification Profile',
    @recipients = 'john@jdoe.com',
    @query = 'SET NOCOUNT ON;SELECT * FROM master.dbo.CommandLog
				WHERE StartTime > DATEADD(day, -7, GETDATE());',
    -- @execute_query_database = 'master',
	@subject = 'Maintenance Statistics',
    @attach_query_result_as_file = 1;

-- when you want SQL Server Agent to send mails within jobs
-- enable SQL Server Agent to use the Database Mail profile (in the Alert System tab)
-- don't forgt to restart the SQL Server Agent
EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DBA Notification Profile',
 N'REG_SZ', N'SQL Server Database Mail Notifications';
GO

-- create an operator
EXECUTE msdb.dbo.sp_add_operator
	@name = N'SQL Server DBAs',
	@enabled = 1, 
	@weekday_pager_start_time = 90000, 
	@weekday_pager_end_time = 180000, 
	@saturday_pager_start_time = 90000, 
	@saturday_pager_end_time = 180000, 
	@sunday_pager_start_time = 90000, 
	@sunday_pager_end_time = 180000, 
	@pager_days = 0, 
	@email_address = N'john@jdoe.com', 
	@category_name = N'[Uncategorized]';
GO

-- create a failsafe operator
EXECUTE msdb.dbo.sp_add_operator
	@name = N'SQL Server Failsafe Operator',
	@enabled = 1, 
	@weekday_pager_start_time = 90000, 
	@weekday_pager_end_time = 180000, 
	@saturday_pager_start_time = 90000, 
	@saturday_pager_end_time = 180000, 
	@sunday_pager_start_time = 90000, 
	@sunday_pager_end_time = 180000, 
	@pager_days = 0, 
	@email_address = N'jane@jdoe.com', 
	@category_name = N'[Uncategorized]';
GO

-- add a failsafe operator
EXECUTE master.dbo.sp_MSsetalertinfo
	@failsafeoperator = N'SQL Server Failsafe Operator', 
	@notificationmethod = 1;  -- email
GO
EXECUTE msdb.dbo.sp_set_sqlagent_properties
	@email_save_in_sent_folder = 1, 
	@alert_replace_runtime_tokens = 1, -- enable when you want your jobs to respond to agent alerts
	@databasemail_profile = N'DBA Notification Profile', 
	@use_databasemail = 1;
GO

-- configure the SQL Server Agent history retention
EXECUTE msdb.dbo.sp_set_sqlagent_properties
	@jobhistory_max_rows = 10000;
GO

-- view the mail account and profile
SELECT * FROM msdb.dbo.sysmail_account;
SELECT * FROM msdb.dbo.sysmail_profile;

-- delete the mail account and profile
/*
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'DBA Notification Account';
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'DBA Notification Profile';

SELECT * FROM msdb.dbo.sysmail_account;
SELECT * FROM msdb.dbo.sysmail_profile;
*/